<?php

m2import("phpinchains/util/db/DataSource.php");
m2import("phpinchains/util/db/SequenceEntity.php");
m2import("phpinchains/util/db/EntityException.php");
m2import("phpinchains/util/db/JoinTable.php");
m2import("phpinchains/util/ConvertHelper.php");


/**
 * Base class for db entities
 * 
 * Entity Classes have to be named like: <TableName-with-capital-first-letter>Entity
 * 
 */
class phpinchains_util_db_DbEntityBase {

    /**
     *
     * @var udba
     */
    protected $con;
    var $table;
    var $fields = array(
    // field with index 0 is considered primary key
    //    	0 => array('id', 'int', 'validator'[, 'validationParam']),
    //    	1 => array('field', 'type', 'validator'[, 'validationParam'])
    );
    var $blobs = array (
    //            'field1', 'field2'
    );
    var $fieldsReversed;
    var $foreignKeys = array(
    //    	'field' => 'EntityClass' or
    //    	'field' => array('EntityClass', 'prefix')
    );
    var $joinTables = array(
    // array('joinTableClass' => 'EntityClass', 'connectedClass' => 'EntityClass' 'thisfield' => 'field1', 'otherfield' => 'field2')
    // joinTableclass:   Entity class of join table
    // connectedClass:   Entity class of connected table
    // thisfield:        'Virtual' field in this table (is created according to this configuration
    // otherfield:       Field in the connection table referencing data sets in this table
    );
    var $joinTableObjects = array();
    var $foreignKeyObjects = array();
    var $index = array();
    // default values of fields
    var $defaultvalues = array(
    //      'fieldname' => '<defaultvalue>'
    );
    var $uniqueindex = array();
    var $versionfields = null;
    var $actionform;
    var $validationErrors;
    var $selfref = null;
    var $pathfield = null;
    var $usetransactions = false;

    
    public static $JOIN_INNER = 'inner';
    public static $JOIN_LEFT  = 'left';
    
    var $jointype = 'left';

    var $hasPrimaryKey = false;

    /**
     *
     * @var phpinchains_util_db_Preprocessor
     */
    var $preprocessor = null;
    /**
     *
     * @var phpinchains_util_db_Postprocessor
     */
    var $postprocessor = null;

    var $versiondatereset = '9998-12-31 59:59:59';
    var $versiondate      = '9998-12-31 59:59:59';
    var $versioncreateend = '9999-01-01 00:00:00';

    protected static $joinTableCache = array();

        /**
     * Constructor
     *
     * @param phpinchains_ActionForm $actionform Actionform instance for validation (not yet implemented -> at the moment only via gui)
     * @param phpinchains_util_db_Preprocessor  $preprocessor Used to preprocess values before saving
     * @param phpinchains_util_db_Postprocessor $postprocessor Used to postprocess values after loading
     */
    function phpinchains_util_db_DbEntityBase(phpinchains_ActionForm $actionform, phpinchains_util_db_Preprocessor $preprocessor = null, phpinchains_util_db_Postprocessor $postprocessor = null) {
        $this->actionform = & $actionform;
        $this->preprocessor  = $preprocessor;
        $this->postprocessor = $postprocessor;
        $this->validationErrors = & $actionform->get('validationErrors');
        foreach ($this->fields as $field)
            $this->fieldsReversed[$field[0]] = $field[1];
        $ds = phpinchains_util_db_DataSource::getInstance();

        // figure out if first column is a primary key
        foreach ($this->uniqueindex as $ui) {
            if (count($ui) == 1 && $ui[0] == $this->fields[0][0]) {
                $this->hasPrimaryKey = true;
                break;
            }
        }

        $this->foreignKeyObjects = array();
        foreach ($this->foreignKeys as $n => $v) {
            $classname = is_array($v)?$v[0]:$v;
            if (class_exists($classname)) {
                if (strtolower($classname) == strtolower(get_class($this))) {
                    $this->foreignKeyObjects[$n] = $this;
                } else {
                    $this->foreignKeyObjects[$n] = new $classname($actionform);
                }
            }
            else
                print "Class $classname is missing in ".get_class($this)."!";
        }
        foreach ($this->joinTables as $jt) {
            $classname = $jt['connectedClass'];
            if (class_exists($classname)) {
                $joinTableId = strtolower($jt['joinTableClass']).'_'.strtolower($jt['connectedClass']);
                if (self::$joinTableCache[$joinTableId]) {
                    $this->joinTableObjects[$jt['otherfield']] = self::$joinTableCache[$joinTableId];
                } else {
                    $this->joinTableObjects[$jt['otherfield']] = new phpinchains_util_db_JoinTable($jt['joinTableClass'], $jt['connectedClass'], $jt['thisfield'], $jt['otherfield']);
                    self::$joinTableCache[$joinTableId] = $this->joinTableObjects[$jt['otherfield']];
                    $this->joinTableObjects[$jt['otherfield']]->init();
                }
            }
            else
                print "Class $classname is missing in ".get_class($this)."!";
        }
        $this->con = $ds->getConnection();
    }
    
    function setJoinType ($type) {
        $this->jointype = $type;
    } 
    
    function getJoinType () {
        return $this->jointype;
    } 

    /**
     * Get error number returned by the database
     * 
     * @return int Error number
     */
    function getErrorNo() {
        return $this->con->getErrorNo();
    }

    function getErrorType() {
        return $this->con->getErrorType($this->con->getErrorNo());
    }
    
    /**
     * Get error message returned by the database
     * 
     * @return string Error message
     */
    function getError() {
        return $this->con->getErrorNo();
    }

    /**
     *
     * @return string Name of the table of the entity
     */
    function getTableName () {
        return $this->table;
    }

    /**
     *
     * @return string Name of the table of the entity
     */
    function getSelfref () {
        return $this->selfref;
    }

    /**
     *
     * @return udba
     */
    function getConnection() {
        return $this->con;
    }

    /**
     * Checks, if a field exists in the entities table
     *
     * @param string $field                   Name of the field to check
     * @param bool   $checkForeignKeyRelated  Check the field of the tables related by foreign key also?
     * @return bool
     */
    function hasField($field, $checkForeignKeyRelated = false, $prefix = '') {
        if ($prefix)
            $field = str_replace($prefix.'_','', $field);
        foreach ($this->fields as $f) {
            if ($f[0] == $field) {
                return true;
            }
        }
        if ($checkForeignKeyRelated) {
            foreach ($this->foreignKeyObjects as $n => $o) {
                if ($o->hasField($field,$false, (is_array($this->foreignKeys[$n])?$this->foreignKeys[$n][1]:''))) {
                    return true;
                }
            }
        }
        return false;
    }

    function isBlob($name) {
        return in_array($name, $this->blobs);
    }

    function isForeignKey($name) {
        return array_key_exists($name, $this->foreignKeys);
    }

    private function _switch (&$data, $name, $type = "") {
        $d = $data[$name];
        if (is_array($d)) {
            $type = $d[0];
            $d    = $d[1];
        }
        if (!$type) {
            $type = $this->fieldsReversed[$name];
        }
        // FIXME: War &&. Wozu? Tieferer Sinn?
        if ($d == null || !strlen($d.""))
            if (isset($this->defaultvalues[$name]))
                $d = $this->defaultvalues[$name];
        // If is a foreign key an is not filled out
        if (($d==null || !strlen($d."")) && $this->isForeignKey($name))
            return "NULL";
        switch ($type) {
            case 'int':
                $valueStr = (int) $d;
                break;
            case 'float':
                $valueStr = (float) $d;
                break;
            case 'double':
                $valueStr = (double) $d;
                break;
            case 'decimal':
                $valueStr = (preg_match('/^\d+\.\d+$/', $d)?$d:(preg_match('/^\d+,\d+$/', $d)?str_replace(',', '.', $d):(double)$d));
                break;
            case 'date':
                $valueStr = $this->con->quote(ConvertHelper::toSQLDate($d));
                break;
            case 'time':
                $valueStr = $this->con->quote(ConvertHelper::toTime($d));
                break;
            case 'datetime':
                $valueStr = $this->con->quote(ConvertHelper::toDateTime($d));
                break;
            case 'sql':
                $valueStr = $d;
                break;
            case 'blob':
                if ($d) {
                    $fcontent = $d;
                } else {
                    $env = env::getInstance();
                    $f = $env->getFile($name);
                    $fcontent = file_get_contents($f['tmp_name']);
                }
                $valueStr = $this->con->quote($fcontent);
                break;
            case 'string':
            default:
                $valueStr = $this->con->quote($d);
                break;
        }
        return $valueStr;
    }

    /**
     * Create data row from assoz. array.
     *
     * Fields are automatically handled: numbers are casted, strings are quoted and escaped => sql injection protection.
     *
     * @param array $data Assoz array with fields. Not given values are set null.
     * @return int  Insert id.
     */
    function create($data, $showquery = false) {
        if ($this->preprocessor != null) {
            $data = $this->preprocessor->processCreate($data);
        }

        // selfref path
        if ($this->selfref && $this->pathfield) {
            $parent = $this->findByPrimaryKey((int) $data[$this->selfref]);
            if ($parent) {
                $data[$this->pathfield] = $parent[$this->pathfield].$parent[$this->fields[0][0]].'/';
            } else {
                $data[$this->pathfield] = '/0/';
            }
        }

        $query = "insert into ".$this->table." ";
        $fieldStr = '';
        $valueStr = '';
        // if versionfields given, use sequence
        if ($this->versionfields != null) {
            $seq = new phpinchains_util_db_SequenceEntity($this->table.'_versionsequence', $this->actionform);
            $id = $seq->create();
            $fieldStr = 'id,'.$this->versionfields['start'].','.$this->versionfields['end'].',';
            $valueStr = $id.",'".date('Y-m-d H:i:s', time())."','".$this->getVersionCreateEnd()."',";
        }
        $first = true;
        // skip first field while creating, if table has a one column primary key or is a versioned table
        $startwith = ($this->hasPrimaryKey || $this->versionfields != null)?1:0;
        for ($i = $startwith; $i < count($this->fields); $i++) {
            if ($this->versionfields != null &&
                    ($this->fields[$i][0] == $this->versionfields['start'] || $this->fields[$i][0] == $this->versionfields['end'])) {
                continue;
            }
            $fieldStr .= ($first?'':',').$this->fields[$i][0];
            $valueStr .= ($first?'':',').$this->_switch($data, $this->fields[$i][0], $this->fields[$i][1]);
            $first = false;
        }
        $this->con->query($query.'('.$fieldStr.') values ('.$valueStr.');', $showquery);
        if (MODEL2_DB_DEBUG_MODE && $this->con->getErrorNo()) {
            print $this->con->getError();
        } 
        if ($this->versionfields == null) {
            $id = $this->con->insert_id();
        }
        // create n:m related relations
        if (count($this->joinTableObjects)) {
            foreach($this->joinTableObjects as $n => $jt) {
                if (is_array($data[$n]))
                    $jt->create($id, $data[$n], $showquery);
            }
        }
        return $id;
    }

    function update($data, $showquery = false) {
        if ($this->versionfields != null) {
            return $this->update_versions($data, $showquery);
        } else {
            return $this->update_normal($data, $showquery);
        }
    }

    /**
     * Update method for normal tables
     *
     * @param array $data Assoz array with data
     * @param bool  $showquery Print out the query
     * @return bool Has an error occured?
     */
/*    function update_normal ($data, $showquery = false) {
        if ($this->preprocessor != null) {
            $data = $this->preprocessor->processUpdate($data);
        }

        // selfref path
        if ($this->selfref && $this->pathfield) {
            $obj = $this->findByPrimaryKey($data[$this->fields[0][0]]);
            if ($data[$this->selfref] != $obj[$this->selfref]) {
                $newparent = $this->findByPrimaryKey((int) $data[$this->selfref]);
                $olddata   = $this->findByPrimaryKey((int) $data[$this->fields[0][0]], array('fields' => $this->pathfield));
                $oldpath   = $olddata[$this->pathfield];
                if ($newparent) {
                    $newpath = $newparent[$this->pathfield].$newparent[$this->fields[0][0]].'/';
                } else {
                    $newpath = '/0/';
                }
                $data[$this->pathfield] = $newpath;
                $this->query('update '.$this->table.' set '.$this->pathfield." = REPLACE($this->pathfield, '$oldpath', '$newpath') where $this->pathfield like '$oldpath{$data[$this->fields[0][0]]}/%';");
            }
        }

        if (!isset($data[$this->fields[0][0]]))
            return;
        $query = "update ".$this->table." set ";
        $valueStr = '';
        $first = true;
        $keys = array_keys($data);
        for ($i = 1; $i < count($this->fields); $i++) {
            if (in_array($this->fields[$i][0], $keys)) // using keys, because isset does not work properly if value is null
            {
                $valueStr .= ($first?'':',').$this->fields[$i][0].'='.$this->_switch($data, $this->fields[$i][0], $this->fields[$i][1]);
                $first = false;
            }
        }
        $this->con->query($query.$valueStr.' where '.$this->fields[0][0].'='.
            $this->_switch($data, $this->fields[0][0], $this->fields[0][1]).';', $showquery);
        return (!$this->con->getErrorNo());
    }*/

    function update_normal ($data, $showquery = false) {
        if (!isset($data[$this->fields[0][0]]))
            return; // FIXME: return Exception
        $this->updateWhere($data, $this->fields[0][0].'='.
            $this->_switch($data, $this->fields[0][0], $this->fields[0][1]), $showquery);
    }

    function updateVersion ($data, $showquery = false) {
        if (!isset($data[$this->fields[0][0]]) || !isset($data[$this->versionfields['end']]))
            return; // FIXME: return Exception
        $this->updateWhere($data, $this->fields[0][0].'='.
            $this->_switch($data, $this->fields[0][0], $this->fields[0][1]).
                    ' and '.$this->versionfields['end'].'='.
            $this->_switch($data, $this->versionfields['end'], 'datetime'), $showquery);
    }

    /**
     * Update method with specified where clause
     *
     * @param array $data Assoz array with data
     * @param bool  $showquery Print out the query
     * @return bool Has an error occured?
     */
    function updateWhere ($data, $whereclause, $showquery = false) {
        if ($this->preprocessor != null) {
            $data = $this->preprocessor->processUpdate($data);
        }
        if ($this->usetransactions) {
            try {
                $this->con->beginTransaction();
            } catch (phpinchains_util_db_EntityException $e) {
                print $e->getMessage();
            }
        }
        // selfref path
        if ($this->selfref && $this->pathfield) {
            $obj = $this->findByPrimaryKey($data[$this->fields[0][0]]);
            if ($data[$this->selfref] != $obj[$this->selfref]) {
                $newparent = $this->findByPrimaryKey((int) $data[$this->selfref]);
                $olddata   = $this->findByPrimaryKey((int) $data[$this->fields[0][0]], array('fields' => $this->pathfield));
                $oldpath   = $olddata[$this->pathfield];
                if ($newparent) {
                    $newpath = $newparent[$this->pathfield].$newparent[$this->fields[0][0]].'/';
                } else {
                    $newpath = '/0/';
                }
                $data[$this->pathfield] = $newpath;
                $this->query('update '.$this->table.' set '.$this->pathfield." = REPLACE($this->pathfield, '$oldpath', '$newpath') where $this->pathfield like '$oldpath{$data[$this->fields[0][0]]}/%';");
                if (MODEL2_DB_DEBUG_MODE && $this->con->getErrorNo()) {
                    print $this->con->getError();
                } 
            }
        }

        $query = "update ".$this->table." set ";
        $valueStr = '';
        $first = true;
        $keys = array_keys($data);
        for ($i = 1; $i < count($this->fields); $i++) {
            if (in_array($this->fields[$i][0], $keys)) // using keys, because isset does not work properly if value is null
            {
                $valueStr .= ($first?'':',').$this->fields[$i][0].'='.$this->_switch($data, $this->fields[$i][0], $this->fields[$i][1]);
                $first = false;
            }
        }
        $this->con->query($query.$valueStr.' where '.$whereclause.';', $showquery);
        if (MODEL2_DB_DEBUG_MODE && $this->con->getErrorNo()) {
            print $this->con->getError();
        } 
        // update n:m related relations
        if (count($this->joinTableObjects)) {
            foreach($this->joinTableObjects as $n => $jt) {
                if (is_array($data[$n]))
                    $jt->update($data['id'], $data[$n], $showquery);
            }
        }
        if ($this->usetransactions) {
            try {
                $this->con->commitTransaction();
            } catch (phpinchains_util_db_EntityException $e) {
                print $e->getMessage();
            }
        }
        return (!$this->con->getErrorNo());
    }

    /**
     * Update method for versioned tables
     *
     * @param array $data Assoz array with data
     * @param bool  $showquery Print out the query
     * @return bool Has an error occured?
     */
    function update_versions ($data, $showquery = false) {
        if ($this->preprocessor != null) {
            $data = $this->preprocessor->processUpdate($data);
        }
        if ($this->usetransactions) {
            try {
                $this->con->beginTransaction();
            } catch (phpinchains_util_db_EntityException $e) {
                print $e->getMessage();
            }
        }

/* FIXME: currently no path supported with versions
        // selfref path
        if ($this->selfref && $this->pathfield) {
            $obj = $this->findByPrimaryKey($data[$this->fields[0][0]]);
            if ($data[$this->selfref] != $obj[$this->selfref]) {
                $newparent = $this->findByPrimaryKey((int) $data[$this->selfref]);
                $olddata   = $this->findByPrimaryKey((int) $data[$this->fields[0][0]], array('fields' => $this->pathfield));
                $oldpath   = $olddata[$this->pathfield];
                if ($newparent) {
                    $newpath = $newparent[$this->pathfield].$newparent[$this->fields[0][0]].'/';
                } else {
                    $newpath = '/0/';
                }
                $data[$this->pathfield] = $newpath;
                $this->query('update '.$this->table.' set '.$this->pathfield." = REPLACE($this->pathfield, '$oldpath', '$newpath') where $this->pathfield like '$oldpath{$data[$this->fields[0][0]]}/%';");
            }
        }
*/
        if (!isset($data[$this->fields[0][0]]))
            return;
        $query = "insert into ".$this->table." ";
        $time = time();
        $this->con->query("update ".$this->table." set ".$this->versionfields['end']." = '".date('Y-m-d H:i:s', $time-1)."' where ".$this->versionfields['end']." = '".$this->getVersionCreateEnd()."' and id = ".$data[$this->fields[0][0]].";", $showquery);
        if (MODEL2_DB_DEBUG_MODE && $this->con->getErrorNo()) {
            print $this->con->getError();
        } 
        $data[$this->versionfields['start']] = date('Y-m-d H:i:s', $time);
        $data[$this->versionfields['end']] = $this->getVersionCreateEnd();
        $first = true;
        for ($i = 0; $i < count($this->fields); $i++) {
            $fieldStr .= ($first?'':',').$this->fields[$i][0];
            $valueStr .= ($first?'':',').$this->_switch($data, $this->fields[$i][0], $this->fields[$i][1]);
            $first = false;
        }
        $this->con->query($query.'('.$fieldStr.') values ('.$valueStr.');', $showquery);
        if (MODEL2_DB_DEBUG_MODE && $this->con->getErrorNo()) {
            print $this->con->getError();
        } 
        // update n:m related relations
        if (count($this->joinTableObjects)) {
            foreach($this->joinTableObjects as $n => $jt) {
                if (is_array($data[$n]))
                    $jt->update($data['id'], $data[$n], $showquery);
            }
        }
        if ($this->usetransactions) {
            try {
                $this->con->commitTransaction();
            } catch (phpinchains_util_db_EntityException $e) {
                print $e->getMessage();
            }
        }
        return (!$this->con->getErrorNo());
    }

    /**
     * Parameters:
     * cascade:          Also retrieve datasets per foreign key
     * orderby:          Order by field, for random dataset use 'rand()' (gets converted to more effective sql)
     * limit:            Max number of selected rows
     * offset:           Cursor position
     * cascadelimit:     Cascade depth
     * joinreferenced:   Join with all referenced tables
     * joindeep:         Join recursively with all referenced tables
     * join:             Only join given table
     * showquery:        Print out the query
     * showerrors:       Print out the errors, if occured
     * bypassversioning: Bypass automatic handling of versions
     * latestversions:   Load the latest versions of a versioned table, regardless of whether they are deleted or not
     *
     * @param array params
     */
    function findAll($params = array('fields' => "", 'cascade' => false, 'limit' => 0, 'offset' => 0, 'bypassversioning' => false)) {
        return $this->findBy('', '', $params);
    }

    /**
     * Find all versions by primary key from sequence table
     *
     * @param int $pk
     */
    function findVersions ($pk, $params = array('fields' => "", 'cascade' => false, 'limit' => 0, 'offset' => 0)) {
        $params['bypassversioning'] = true;
        return $this->findBy($this->fields[0][0], $pk, $params);
    }

    /**
     * Used to retrieve a single dataset.
     *
     * To retrieve a single dataset of a versioned table use fields of a unique index.
     *
     * @param mixed $pk Scalar value or assoz array with field keys
     * @param array $params Config params
     * @return array Found data set
     */
    function findByPrimaryKey($pk, $params = array('fields' => "", 'cascade' => false, 'limit' => 0, 'offset' => 0)) {
        $prefix = ((isset($params['joindeep']) && $params['joindeep']) || (isset($params['joinreferenced']) && $params['joinreferenced'])) && count($this->foreignKeys)?'R0.':'';
        if (is_array($pk)) {
            $fields = array();
            for ($i = 0; $i < count($this->uniqueindex[0]); $i++) {
                $fields[] = array($this->uniqueindex[0][$i], $pk[$this->uniqueindex[0][$i]]);
            }
            $row = $this->findByFields($fields, 'AND', $params);
        } else {
            $field = $prefix.$this->fields[0][0];
            $rows = $this->findBy($field, $pk, $params);
        }
        return $rows[0];
    }
    
    
    private function selectJoins($join) {
        // join array for rekursive invokation
        $joins = array();
        // select join routes with start point $this->table
        foreach ($join as $je) {
            $parts = explode('.', $je);
            preg_match('/^((\w+):)?(\w+)(>(\w+))?$/', $parts[0], $matches);
            print "<pre>";
            print_r($matches);
            print "</pre>";
            $joins[] = strtolower($parts[0].'Entity');
        }
        return $joins;
    }
    
    private function prepareJoinArray($table, $join) {
        $joinarray = array();
        foreach ($join as $je) {
            $parts = explode('.', $je);
            if (count($parts) < 2)
                continue;
            preg_match('/^((\w+):)?(\w+)(>(\w+))?$/', $parts[0], $matches);
            if (strtolower($matches[3].'entity') != strtolower($table)) 
                continue;
            array_shift($parts);
            $joinarray[] = join('.', $parts);
        }
        return $joinarray;
    }
    
    private function prepareJoinPart ($p) {
        $parts = explode('.', $p);
        preg_match('/^((\w+):)?(\w+)(>(\w+))?$/', $parts[0], $matches);
        $retVal = array(
            $matches[2],
            $matches[3],
            $matches[5]
        );
        if (!$retVal[0]) {
            foreach ($this->foreignKeys as $n => $v) {
                if (is_array($v) && $v[0] == strtolower($retVal[1].'Entity')) {
                    $retVal[0] = $n;
                    break;
                } elseif (strtolower($v) == strtolower($retVal[1].'Entity')) {
                    $retVal[0] = $n;
                    break;
                }
            }
        }
        return $retVal;
    }

    /**
     *
     * @param bool   $joindeep   Do deep joining?
     * @param int    $count      Count for creating sql alias while deep joining
     * @param srray  $foreignKey Array of $this->table and sql alias of $this->table for deep joining
     * @param mixed  $join       String: Name of table to constain joining to this table
     *                           Array:  Array of strings of tabels to join with:
     *                              R1, R2 => joins table R0 with R1 and R2
     *                              R1.R3, R2 => joins table R0 with R1 and R2 and table R1 with R3
     *                              field:R1>ALIAS1.field2:R2>ALIAS2 => joins R0 with R1 through field renamed to ALIAS1 and R1 with R2 through field2 renamed to ALIAS2
     * @return array Array with all information needed to create join parts: table name, id field, sql alias,
     *      version fields and array of table name, table name of joined table and sql alias of table
     */
    function getKeyRelations($joindeep = false, $count = 0, $foreignKey = array(), $join = '', $alias = '') {
        $hCount = $count;
        $alias = $alias?$alias:"R$hCount";
        if (is_array($join)) {
            $tables[] = array(array($this->table, $this->fields[0][0], $alias, $this->versionfields), $foreignKey);
            if(!count($join)) {
                return $tables;
            }
            /*$joinedTables = $this->selectJoins($join);
            /*foreach ($this->foreignKeyObjects as $n => $v) {
                print "name:$n<br/>";
                $joinedtable = is_array($this->foreignKeys[$n])?$this->foreignKeys[$n][0]:$this->foreignKeys[$n];
                if (in_array(strtolower($joinedtable), $joinedTables)) {
                    // prepared join array for this recursion
                    $recJoin = $this->prepareJoinArray($joinedtable, $join);
                    $tables = array_merge($tables, $this->foreignKeyObjects[$n]->getKeyRelations($joindeep, ++$count, array($this->table, $n, "R".$hCount), $recJoin));
                }
            }*/
            $joinedAliases = array();
            foreach ($join as $v) {
                $joinPart = $this->prepareJoinPart($v);
                $n = $joinPart[0]; // foreign key field
                $nextAlias = $joinPart[2];
                if (in_array($nextAlias, $joinedAliases))
                    continue;
                $joinedAliases[] = $nextAlias;
                $joinedtable = is_array($this->foreignKeys[$n])?$this->foreignKeys[$n][0]:$this->foreignKeys[$n];
                $recJoin = $this->prepareJoinArray($joinedtable, $join);
                $tables = array_merge($tables, $this->foreignKeyObjects[$n]->getKeyRelations($joindeep, ++$count, array($this->table, $n, $alias), $recJoin, $nextAlias));
            }
            
        } else {
            $tables[] = array(array($this->table, $this->fields[0][0], "R".$hCount, $this->versionfields), $foreignKey);
            if ($joindeep || $count == 0) {
                foreach ($this->foreignKeyObjects as $n => $v) {
                    $joinedtable = is_array($this->foreignKeys[$n])?$this->foreignKeys[$n][0]:$this->foreignKeys[$n];
                    if (!$join || strtolower($join).'entity' == strtolower($joinedtable)) {
                        $tables = array_merge($tables, $this->foreignKeyObjects[$n]->getKeyRelations($joindeep, ++$count, array($this->table, $n, "R".$hCount)));
                    }
                }
            }
        }
        return $tables;
    }

    /**
     * Generate fields string for select query
     *
     * @param enum(null,'ref','deep') $joined
     * @param boolean $includekey Include key column in field list, although $joined is 'false'.
     * @param unknown_type $count
     * @return unknown
     */
    function getFields ($joined, $includekey = false, & $count = 0, $prefix = '') {
        $f = array();
        $startwith = ($includekey?0:1);
        for ($i = $startwith; $i < count($this->fields); $i++) {
            if (!in_array($this->fields[$i][0], $this->blobs))
                $f[] = $this->fields[$i][0];
        }
        $fields = array('');
        $first = true;
        foreach ($f as $fe) {
            if ($first) $first = false;
            else $fields[0] .= ', ';
            $fields[0]      .= "R$count.".$fe.($prefix?' as '.$prefix.'_'.$fe:'');
        }
        if (count($this->blobs)) {
            $blobs = array();
            foreach ($this->blobs as $blob)
                $blobs[] = "Length(R$count.$blob) as $blob";
            $fields[0] .= (strlen($fields[0])?', ':'').join(",", $blobs);
        }
        $count++;
        if ($joined != null) {
            foreach ($this->foreignKeyObjects as $n => $v) {
                $fields[] = $this->foreignKeyObjects[$n]->getFields($joined=='ref'?null:'deep', false, $count, is_array($this->foreignKeys[$n])?$this->foreignKeys[$n][1]:'');
            }
        }
        if ($this->selfref != null) { // CHP == CHeck Parent
            $fields[] = '(select count(*) from '.$this->table.' CHP'.$count.' where CHP'.$count.'.'.$this->selfref.'=R0.id) as '.$prefix.'is_parent';
        }
        return join(', ', $fields);
    }

    function setVersionDate($date) {
        $this->versiondate = $date;
    }

    function resetVersionDate($date) {
        $this->versiondate = $this->versiondatereset;
    }

    function getVersionDate() {
        return $this->versiondate;
    }

    function getVersionCreateEnd() {
        return $this->versioncreateend;
    }

    /**
     * Get array of version field names
     */
    function getVersionFields () {
        return array($this->versionfields['start'], $this->versionfields['end']);
    }

    /**
     * Get the from and the where clause of the query use for joining related tables.
     *
     * @param bool $joindeep Join all tables related with this table whether direct or indirect
     * @param array $params  Params for join parts:
     *      bypassversioning (bool) = find all versions,
     *      latestversions (bool) = load latest versions of joined tables, even if they are deleted
     *      join (string) = Only join with the given table
     * @return array Array with index 'from' for from clause and index 'where' for where clause
     */
    function getJoinParts($joindeep = false, $params = array()) {
        // if join table given, restrict to it
        $tables = $this->getKeyRelations($joindeep, 0, array(), (isset($params['join'])?$params['join']:null));
        $versiondate = $this->getVersionDate();
        $latestVersionFrom = "";
        // Prepend join with latest version select for retrieval of latest versions of all datasets (show deleted versions also)
        if ((!isset($params['bypassversioning']) || !$params['bypassversioning']) && $this->versionfields != null) {
            if (isset($params['latestversions']) && $params['latestversions']) {
                // selects the latest version ids and enddates for joining
                $latestVersionFrom = '(SELECT id, MAX( '.$this->versionfields['end'].' ) as maxend FROM '.$this->table.' GROUP BY id) LATEST LEFT JOIN '.$tables[0][0][0].' R0 on (R0.id = LATEST.id and R0.'.$this->versionfields['end'].' = LATEST.maxend)';
            }
        }
        if ($this->jointype == 'inner') {
            $from  = ($latestVersionFrom?$latestVersionFrom:$tables[0][0][0].' R0');
            $where = "";
            for ($i = 1; $i < count($tables); $i++) {
                $from  .= ','.$tables[$i][0][0].' '.$tables[$i][0][2];
                $where .= ($i>1?' and ':'').$tables[$i][0][2].'.'.$tables[$i][0][1].'='.$tables[$i][1][2].'.'.$tables[$i][1][1];
                if ($tables[$i][0][3] != null) {
                    $where .= ' and '.$tables[$i][0][2].'.'.$tables[$i][0][3]['start']."<='$versiondate' and ".$tables[$i][0][2].'.'.$tables[$i][0][3]['end'].">='$versiondate'";
                }
            }
            return array('from' => $from, 'where' => $where);
        } else {
            $from  = ($latestVersionFrom?$latestVersionFrom:$tables[0][0][0].' R0');
            $where = "";
            for ($i = 1; $i < count($tables); $i++) {
/*                $from  .= ' LEFT JOIN '.$tables[$i][0][0].' '.$tables[$i][0][2].' ON ('.$tables[$i][0][2].'.'.$tables[$i][0][1].'='.$tables[$i][1][2].'.'.$tables[$i][1][1].')';
                if ($tables[$i][0][3] != null) {
                    $where .= (strlen($where)?' and ':'').
                        '('.$tables[$i][0][2].'.'.$tables[$i][0][1].' is NULL or ('.$tables[$i][0][2].'.'.$tables[$i][0][3]['start']."<='$versiondate' and ".
                        $tables[$i][0][2].'.'.$tables[$i][0][3]['end'].">='$versiondate'))";
                }*/
                $from  .= ' LEFT JOIN '.$tables[$i][0][0].' '.$tables[$i][0][2].' ON ('.$tables[$i][0][2].'.'.$tables[$i][0][1].'='.$tables[$i][1][2].'.'.$tables[$i][1][1];
                if ($tables[$i][0][3] != null) {
                    $from .= ' and '.
                        $tables[$i][0][2].'.'.$tables[$i][0][3]['start']."<='$versiondate' and ".
                        $tables[$i][0][2].'.'.$tables[$i][0][3]['end'].">='$versiondate'";
                }
                $from .= ')';
            }
            return array('from' => $from, 'where' => $where);
        }
    }

    /**
     * Duplicate a data set. Not version proof!
     *
     * @param int $id
     * @param array $overrides Assoz array with override value for the duplicated dataset.
     */
    function duplicate($id, $overrides = array()) {
        $f = $this->fields;
        array_shift($f);
        $fields = array();
        foreach ($f as $field) {
            $fields[] = $field[0];
        }
        $fieldsstr = join(',', $fields);

        $selectfields = array();
        foreach ($f as $field) {
            if (isset($overrides[$field[0]])) {
                $selectfields[] = $this->_switch($overrides, $field[0]);
            } else {
                $selectfields[] = $field[0];
            }
        }
        $selectfieldsstr = join(',', $selectfields);
        $this->query('insert into '.$this->getTableName().' ('.$fieldsstr.') select '.$selectfieldsstr.' from '.$this->getTableName().' where id = '.((int) $id).';');
        if (MODEL2_DB_DEBUG_MODE && $this->con->getErrorNo()) {
            print $this->con->getError();
        } 
    }

    function findBy($field, $value, $params = array('fields' => "", 'cascade' => false, 'limit' => 0, 'offset' => 0)) {
        if (strlen($field) && strlen($value)) {
            $fields = array(array($field, $value));
        } else {
            $fields = array();
        }
        return $this->findByFields($fields, 'AND', $params);
    }

    /**
     * Search a fulltext index.
     *
     * @param string $index Name of the index.
     * @param string $query Query
     * @param array $params
     */
    function findByFulltext($index, $query, $params = array('fields' => "", 'cascade' => false, 'limit' => 0, 'offset' => 0)) {
        return $this->findByFields(array(
        "MATCH (".join(',', $this->index[$index]).") AGAINST (".$this->con->quote($query).")"
        ), 'AND', $params);
    }

    /**
     * Search a fulltext index.
     *
     * @param string $index Name of the index.
     * @param string $query Query
     * @param array $params
     */
    function findByFulltextBoolean($index, $query, $params = array('fields' => "", 'cascade' => false, 'limit' => 0, 'offset' => 0)) {
        return $this->findByFields(array(
        "MATCH (".join(',', $this->index[$index]).") AGAINST (".$this->con->quote($query)." IN BOOLEAN MODE)"
        ), 'AND', $params);
    }

    /**
     * Search a fulltext index.
     *
     * @param string $index Name of the index.
     * @param string $query Query
     * @param array  $fields Additional fields for where clause
     * @param string $combination Combination for fields
     * @param array $params
     */
    function findByFulltextFields($index, $query, $fields = array(), $combination = "AND", $params = array('fields' => "", 'cascade' => false, 'limit' => 0, 'offset' => 0)) {
        $fields[] = "MATCH (".join(',', $this->index[$index]).") AGAINST (".$this->con->quote($query).")";
        return $this->findByFields($fields, $combination, $params);
    }

    /**
     * Create a match condition for a findByFields query
     *
     * @param string $index Name of the index to query
     * @param string $query The query itself, resp. the search words
     * @param string $tablealias Table alias to use (e.g. R0)
     * @return string the created match condition sql code
     */
    function getMatchBoolean ($index, $query, $tablealias = '') {
        return "MATCH (".($tablealias?$tablealias.'.':'').join(','.($tablealias?$tablealias.'.':''), $this->index[$index]).") AGAINST (".$this->con->quote($query)." IN BOOLEAN MODE)";
    }

    /**
     * Get row count of the table
     *
     * @return int The row count
     */
    function count () {
        $count = $this->findAll(array('fields' => 'count(*) as count'));
        return $count[0]['count'];
    }

    /**
     * Search a fulltext index.
     *
     * @param string $index Name of the index.
     * @param string $query Query
     * @param array  $fields Additional fields for where clause
     * @param string $combination Combination for fields
     * @param array  $params
     */
    function findByFulltextBooleanFields($index, $query, $fields = array(), $combination = "AND", $params = array('fields' => "", 'cascade' => false, 'limit' => 0, 'offset' => 0)) {
        $fields[] = $this->getMatchBoolean($index, $query);
        return $this->findByFields($fields, $combination, $params);
    }

    private function getGenFields ($params) {
        if (!empty($params['joinreferenced'])) {
            return $this->getFields('ref',true);
        } elseif (!empty($params['joindeep'])) {
            return $this->getFields('deep',true);
        } else {
            return $this->getFields(null, true);
        }
    }

    /**
     * Shortcut for option latestversions=true
     *
     * @param <type> $fields
     * @param <type> $combination
     * @param <type> $params
     */
    function findByFieldsLatestVersions($fields, $combination, $params = array('fields' => "", 'cascade' => false, 'limit' => 0, 'offset' => 0)) {
        $params['latestversions'] = true;
        $this->findByFields($fields, $combination, $params);
    }

    function whereClause($fields, $combination) {
        $fieldsStr = "";

        $fields_hold = $fields;
        $fields = array();
        foreach ($fields_hold as $field) {
            if (is_array($field) && count($field)>0) {
                $fields[] = $field;
            } elseif (strlen($field)>0) {
                $fields[] = $field;
            }
        }

        for ($i = 0; $i < count($fields); $i++) {
            if ($i != 0)
                $fieldsStr .= " ".$combination." ";
            if (is_array($fields[$i])) {
                if (isset($fields[$i][2]) && (strtolower($fields[$i][2]) == 'in' || strtolower($fields[$i][2]) == 'not in')) {
                    $fieldsStr .= $fields[$i][0]." ".$fields[$i][2]." (".$fields[$i][1].")";
                } else {
                    $data = array($fields[$i][0] => $fields[$i][1]);
                    $fieldsStr .= $fields[$i][0].
                        " ".(isset($fields[$i][2]) ? $fields[$i][2] : '=')." ".$this->_switch($data, $fields[$i][0]);
                }
            } else
                $fieldsStr .= $fields[$i];
        }
        if ($fieldsStr)
            return '('.$fieldsStr.')';
    }

    /**
     *
     * Handles Order By RAND() => random not in db, but in PHP => using RAND() for ordering with limit is save
     *      can also use: $params['randomdatasets'] = <number of random datasets>
     *
     * example:
     *
     * $entity->findByFields(array(
     * 	array('field1', 'value1'), // => treat value as type of field1, results in field1='value1'
     *  array('field2', 'value2%', 'LIKE'), // results in field2 LIKE 'value2%'
     *  "MATCH (field3, field4) AGAINST ('value')" // inserted as is
     * ), 'AND', $params);
     *
     */
    function findByFields($fields, $combination, $params = array('fields' => "", 'cascade' => false, 'limit' => 0, 'offset' => 0)) {
        $limitStr = '';
        if (!empty($params['limit']) ||!empty($params['offset']))
            $limitStr = ' limit ';
        if (!empty($params['offset'])) {
            $limitStr .= $params['offset'].',';
            if (!$params['limit'])
                $limitStr .= '18446744073709551615';
        }
        if (!empty($params['limit'])) {
            $limitStr .= $params['limit'];
        }
        $orderby = '';
        if (isset($params['orderby']))
            $orderby = ' ORDER BY '.$params['orderby'];
        elseif (isset($params['groupby'])) {
            $orderby = ' GROUP BY '.$params['groupby'];
            if (isset($params['having'])) {
                $orderby .= ' HAVING '.$params['having'];
            }
        }

        if (isset($params['orderby']) && !empty($params['limit']) && strtolower($params['orderby']) == 'rand()') {
            $limitStr = '';
            $orderby  = '';
            $params['randomdatasets'] = $params['limit'];
        }

        $fieldSelect = (!empty($params['fields']) ? $params['fields'] : $this->getGenFields($params));

        $fieldsStr = $this->whereClause($fields, $combination);


        $versionwhere = "";
        // restrict to latest version if versioned (latest undeleted or all, if option 'latestversions' true)
        if ((!isset($params['bypassversioning']) || !$params['bypassversioning']) && $this->versionfields != null) {
            if (isset($params['latestversions']) && $params['latestversions']) {
                // selects the latest version ids and enddates for joining
                $latestVersionFrom = 'left join (SELECT id, MAX( '.$this->versionfields['end'].' ) as maxend FROM '.$this->table.' GROUP BY id) LATEST on (R0.id = LATEST.id and R0.'.$this->versionfields['end'].' = LATEST.maxend) ';
            } else {
                $versiondate = $this->getVersionDate();
                $versionwhere = "R0.".$this->versionfields['start']."<= '$versiondate' and R0.".$this->versionfields['end'].">='$versiondate'";
            }
        }

        if ((isset($params['joinreferenced']) && $params['joinreferenced']) || (isset($params['joindeep']) && $params['joindeep'])
                 || (isset($params['join']) && $params['join'])) {
            $parts = $this->getJoinParts((isset($params['joindeep']) && $params['joindeep']), $params);
            $query = "select $fieldSelect from ".$parts['from'];
            if ($fieldsStr||$parts['where']||$versionwhere) {
                $query .= " where ";
                if ($fieldsStr)
                    if (strtolower($combination) == 'or')
                        $query .= '('.$fieldsStr.') ';
                    else
                        $query .= $fieldsStr.' ';
                if ($fieldsStr && $parts['where'])
                    $query .= ' and ';
                if ($parts['where'])
                    $query .= $parts['where'];
                if (($fieldsStr || $parts['where']) && $versionwhere)
                    $query .= ' and ';
                $query .= $versionwhere;
            }
            $query .= "$orderby$limitStr;";
        }
        else {
            $query = "select $fieldSelect from ".$this->table." R0 ".$latestVersionFrom.(strlen($fieldsStr) || strlen($versionwhere)?'where ':'').
                    (strlen($fieldsStr)?"($fieldsStr)".
                            ((!isset($params['bypassversioning']) || !$params['bypassversioning']) && $this->versionfields != null?' and ':''):'')."$versionwhere $orderby$limitStr;";
        }
        return $this->findByQuery($query, $params);
    }

    function findByQuery ($query, $params = array('cascade' => false, 'limit' => 0, 'offset' => 0)) {
        if ($this->usetransactions && (!empty($params['cascade']) || !empty($params['cascade-selfref']))) {
            try {
                $this->con->beginTransaction();
            } catch (phpinchains_util_db_EntityException $e) {
                print $e->getMessage();
            }
        }
        $result = array();
        $sth = $this->con->query($query, (isset($params['showquery']) && $params['showquery']));
        if (MODEL2_DB_DEBUG_MODE && $this->con->getErrorNo()) {
            print $this->con->getError().'<br/> Query: ' . $query . '<br/><pre>';
            debug_print_backtrace();
            print '</pre><br/><br/>';
        } 
        if (is_object($sth)) {
            if (!empty($params['cascade']) && (!isset($params['cascadelimit']) || $params['cascadelimit']>0)) {
                $result = $this->fetchRowsArray($sth, $params);
                $count = count($result);
                $entities = & $this->foreignKeyObjects;
                $cascadeCache = array();
                for ($i = 0; $i < $count; $i++) {
                    foreach ($this->foreignKeys as $field => $entity) {
                        $p['cascade'] = $params['cascade'];
                        $p['showquery'] = $params['showquery'];
                        if (isset($params['cascadelimit']))
                            $p['cascadelimit'] = $params['cascadelimit']--;
                        if (empty($cascadeCache[$field][$result[$i][$field]]))
                            $cascadeCache[$field][$result[$i][$field]] =
                                $entities[$field]->findByPrimaryKey($result[$i][$field], $p);
                        $result[$i][$field] = & $cascadeCache[$field][$result[$i][$field]];
                    }
                }
            }
            else {
                $result = $this->fetchRowsArray($sth, $params);
            }
            if ($this->selfref && isset($params['cascade-selfref']) && $params['cascade-selfref']) {
                $count = count($result);
                for ($i = 0; $i < $count; $i++) {
                    $p['showquery'] = $params['showquery'];
                    $result[$i][$this->selfref] = $this->findByPrimaryKey($result[$i][$this->selfref], $p);
                }
            }
            if (is_array($this->joinTableObjects)) {
                $count = count($result);
                for ($i = 0; $i < $count; $i++) {
                    foreach ($this->joinTableObjects as $n => $jt) {
                        $result[$i][$n] = $jt->findAll($result[$i]['id'], (isset($params['showquery'])?$params['showquery']:false));
                    }
                }
            }
            if ($this->postprocessor != null) {
                $result = $this->postprocessor->process($result);
            }
        }
        if ($this->usetransactions && (!empty($params['cascade']) || !empty($params['cascade-selfref']))) {
            try {
                $this->con->commitTransaction();
            } catch (phpinchains_util_db_EntityException $e) {
                print $e->getMessage();
            }
        }
        return $result;
    }

    private function fetchRowsArray(statementHandle $sth, $params) {
        if (!empty($params['randomdatasets'])) {
            $num_rows = $sth->num_rows();
            if ($params['randomdatasets'] > 0) {
                if ($params['randomdatasets'] > $num_rows) {
                    $params['randomdatasets'] = $num_rows;
                }
                $rows = array();
                $retrieved = array();
                while (count($rows) < $params['randomdatasets']) {
                    do {
                        $rand = rand(0, $num_rows-1);
                    } while (in_array($rand, $retrieved));
                    $sth->setCursor($rand);
                    $rows[] = $sth->fetch_row_array();
                    $retrieved[] = $rand;
                }
                $sth->free();
                return $rows;
            }
            return array();
        } else {
            return $sth->fetch_rows_array();
        }
    }

    function query ($query) {
        $this->con->query($query);
        if (MODEL2_DB_DEBUG_MODE && $this->con->getErrorNo()) {
            print $this->con->getError();
        } 
        return (!$this->con->getErrorNo());
    }

    /**
     * Delete one or more datasets. Every unique index may be used to delete.
     *
     * To delete one row with single column key:
     * $entity->delete(5);
     * To delete one row with multiple column key:
     * $entity->delete(array('keycol1' => <keyval1>, 'keycol2' => <keyval2>, ...));
     * To delete many rows with single column key:
     * $entity->delete(array(1,2,3,4,...));
     *
     * @param mixed $pk Scalar int value as key or array with int keys or array of field arrays like 'findByFields'
     * @param bool $showquery Show generated query?
     * @return bool Has error?
     */
    function delete ($pk, $showquery = false) {
        if (is_array($pk)) {
            // if pk array is field array. than check against a unique index.
            if (!isset($pk[0])) {
                $ui = null;
                $keys = array_keys($pk);
                foreach ($this->uniqueindex as $idx) {
                    if (array_key_exists($keys[0], $idx)) {
                        $ui = $idx;
                        break;
                    }
                }
                if ($ui == null) return false;
                $fields = array();
                foreach ($idx as $col) {
                    $fields[] = array($col, $pk['col']);
                }
                // no date needed, because we use as unique index.
                $params['bypassversioning'] = true;
                return $this->deleteByFields($fields, 'AND');
                // Do not delete from join table here, cause automatic join tables do not work with composite keys or unique indexes
            }
            $in = "";
            for ($i = 0; $i < count($pk); $i ++)
                $pk[$i] = (int) $pk[$i];
            $where = " in (".join(',',$pk).")";
        } else {
            $where = " = ".((int) $pk);
        }
        if (empty($params['bypassversioning']) && $this->versionfields != null) {
            $this->con->query("update ".$this->table." set ".$this->versionfields['end']." = '".date('Y-m-d H:i:s', time()-1)."' where ".$this->versionfields['end']." = '".$this->getVersionCreateEnd()."' and id $where;");
            if (MODEL2_DB_DEBUG_MODE && $this->con->getErrorNo()) {
                print $this->con->getError();
            }
        } else {
            $query = "delete from ".$this->table." where ".$this->fields[0][0].$where.";";
            if ($showquery)
                print $query;
            $this->con->query($query);
            if (MODEL2_DB_DEBUG_MODE && $this->con->getErrorNo()) {
                print $this->con->getError();
            }
            // update n:m related relations
            if (count($this->joinTableObjects)) {
                foreach($this->joinTableObjects as $n => $jt) {
                    $jt->delete($pk, $showquery);
                }
            }
        }
        return (!$this->con->getErrorNo());
    }

    /**
     * Undelete a versioned deleted dataset
     *
     * @param mixed $pk
     * @param bool $showquery
     */
    function undelete ($pk, $showquery = false) {
        if ($this->usetransactions) {
            try {
                $this->con->beginTransaction();
            } catch (phpinchains_util_db_EntityException $e) {
                print $e->getMessage();
            }
        }
        $sth = $this->con->query("select max(".$this->versionfields['end'].") from {$this->table} where id=".(int) $pk);
        if (MODEL2_DB_DEBUG_MODE && $this->con->getErrorNo()) {
            print $this->con->getError();
        } 
        if (is_object($sth)) {
            $row = $sth->fetch_row();
            if ($row[0] != $this->getVersionCreateEnd()) {
                $this->con->query("update {$this->table} set ".$this->versionfields['end']."='".$this->getVersionCreateEnd().
                        "' where id=".(int) $pk." and ".$this->versionfields['end']."='{$row[0]}';");
            }
        }
        if (MODEL2_DB_DEBUG_MODE && $this->con->getErrorNo()) {
            print $this->con->getError();
        } 
        if ($this->usetransactions) {
            try {
                $this->con->commitTransaction();
            } catch (phpinchains_util_db_EntityException $e) {
                print $e->getMessage();
            }
        }
    }

    function deleteByField ($field, $value, $showquery = false) {
        /*$data[$field] = $value;
        if ($this->versionfields != null) {
            $this->con->query("update ".$this->table." set ".$this->versionfields['end']." = '".date('Y-m-d H:i:s', time()-1)."' where ".$this->versionfields['end']." = '".$this->getVersionCreateEnd()."' and ".$field.
                " = ".$this->_switch($data, $field).";", true);
        } else {
            $query = "delete from ".$this->table." where ".$field.
                " = ".$this->_switch($data, $field).";";
            if ($showquery)
                print $query;
            $this->con->query($query);
        }
        return (!$this->con->getErrorNo());*/
        return $this->deleteByFields(array(array($field, $value)), 'AND', $showquery);
    }

    /**
     * Delete datasets by given fields
     * 
     * Example:
     * $this->deleteByFields(array(array($field, $value)), 'AND', $showquery);
     *
     * @param array  $fields Array of arrays with fieldname and value
     * @param string $combination Combination for fields (AND, OR)
     * @param bool   $showquery Print out the generated query
     * @return bool  Error occured?
     */
    function deleteByFields ($fields, $combination, $showquery = false) {
        $where = "";
        foreach ($fields as $field) {
            unset($data);
            $data[$field[0]] = $field[1];
            $where .= (strlen($where)?' '.$combination.' ':'').$field[0].
            " ".(isset($field[2])?$field[2]:'=')." ".$this->_switch($data, $field[0]);
        }
        if (!strlen($where)) return false;
        if (empty($params['bypassversioning']) && $this->versionfields != null) {
            $query = "update ".$this->table." set ".$this->versionfields['end']." = '".date('Y-m-d H:i:s', time()-1)."' where ".'('.$where.') and '.$this->versionfields['end']." = '".$this->getVersionCreateEnd()."';";
        } else {
            $query = "delete from ".$this->table." where ".$where.";";
        }

        if ($showquery)
            print $query;
        $this->con->query($query);
        if (MODEL2_DB_DEBUG_MODE && $this->con->getErrorNo()) {
            print $this->con->getError();
        } 
        return (!$this->con->getErrorNo());
    }

    function remove($pk, $showquery = false) {
        return $this->delete($pk, $showquery);
    }

    /**
     * Is the given field a number type?
     *
     * @param string $field Field name of the field to check
     */
    function isNumberType ($field) {
        $type = $this->fieldsReversed[$field];
        return ($type == 'int' || $type == 'float' || $type == 'double' || $type == 'decimal');
    } 

    /**
     * Create a single csv row
     * 
     * @param array $fields Array of keys or fields, resp.
     * @param array $data   Dataset array
     */
    private function createCSVRow ($fields, $data = null) {
        $row = "";
        if (is_array($fields))
        foreach ($fields as $field) {
            if ($data != null)
                $row .= ','.($this->isNumberType($field)?'':'"').preg_replace('/"/', '\"', $data[$field]).($this->isNumberType($field)?'':'"');
            else
                $row .= ',"'.preg_replace('/"/', '\"', $field).'"';
        }
        $row = substr($row, 1);
        return $row;
    } 
    
    /**
     * Create a csv file from an array of datasets
     * 
     * @param array $fields Array of keys or fields, resp.
     * @param array $data   Array of dataset arrays
     */
    private function createCSV ($fields, $data) {
        $file = "";
        for ($i = 0; $i < count($data); $i++) {
            $file .= $this->createCSVRow($fields, $data[$i])."\n";
        } 
        return $file;
    }
    
    /**
     * Expoert csv file with all datasets in table
     * 
     * @param array $params            Parameters for internal findAll call
     * @param array $fieldtranslation  Assoz array with
     */
    function exportAllCSV ($params = null, $fieldtranslation = null) {
        $rows = array();
        if ($params) {
            $rows = $this->findAll($params);
        } else {
            $rows = $this->findAll();
        }
        if (count($rows)) {
            $keys = array_keys($rows[0]);
            $fields = $keys;
            if ($fieldtranslation != null) {
            	$fields = array();
            	foreach ($keys as $field) {
            		$fields[$field] = $fieldtranslation[$field];
            	}
            }
            $file = $this->createCSVRow($fields)."\n".$this->createCSV($keys, $rows);
            return $file;
        }
    }

    /**
     * Expoert csv file with all datasets in table
     * 
     * @param array $params            Parameters for internal findAll call
     * @param array $fieldtranslation  Assoz array with
     */
    function exportCSV ($fields, $combination, $params = null, $fieldtranslation = null) {
        $rows = array();
        if ($params) {
            $rows = $this->findByFields($fields, $combination, $params);
        } else {
            $rows = $this->findByFields($fields, $combination);
        }
        if (count($rows)) {
            $keys = array_keys($rows[0]);
            $fields = $keys;
            if ($fieldtranslation != null) {
            	$fields = array();
            	foreach ($keys as $field) {
                    $fields[$field] = $fieldtranslation[$field];
            	}
            }
            $file = $this->createCSVRow($fields)."\n".$this->createCSV($keys, $rows);
            return $file;
        }
    }
    
}

?>